package fidus;

import java.sql.*;

public class FidusDBConnect {

    private String column, search;
    private boolean returnall;

    public void setColumn(String column) {
        this.column = column;
    }

    public void setSearch(String search) {
        this.search = search;
    }

    public void setAll(boolean returnall) {
        this.returnall = returnall;
    }

    public boolean isAll() {
        return returnall;
    }

    public ResultSet selectMovie() {
        Connection con = getConnection();
        try {
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String select;
            if (returnall) {
                select = "SELECT * FROM movie ORDER BY Title";
            } else {
                select = "SELECT * FROM movie WHERE " + column + " LIKE '%" + search + "%' ORDER BY Title";
            }
            ResultSet searchResult;
            searchResult = st.executeQuery(select);
            return searchResult;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

    public boolean updateMovie(Movie m, int id) {
        Connection con = getConnection();
        try {
            Statement st = con.createStatement();
            String update = "UPDATE movie "
                    + "SET title = '" + m.title + "', type = '" + m.type + "', genre = '" + m.genre + "', "
                    + "year = '" + m.year + "', price = '" + m.price + "' "
                    + "WHERE id = " + id;

            int result = st.executeUpdate(update);
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return false;
    }

    public boolean insertMovie(Movie m) {
        Connection con = getConnection();
        try {
            Statement st = con.createStatement();
            String insert = "INSERT INTO movie(title, type, genre, year, price) values "
                    + "('" + m.title + "','" + m.type + "','" + m.genre + "','" + m.year + "','" + m.price + "')";

            int result = st.executeUpdate(insert);
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return false;
    }

    public boolean deleteMovie(int id) {
        Connection con = getConnection();
        try {
            Statement st = con.createStatement();
            String delete = "DELETE FROM movie WHERE id = " + id;

            int result = st.executeUpdate(delete);
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return false;
    }

    private static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/fupfidus";
            String user = "root";
            String pw = "sonic400";
            con = DriverManager.getConnection(url, user, pw);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
            System.exit(0);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.exit(0);
        }
        return con;
    }
}